using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Osiptel.HECO.BE;
using System.Data.OracleClient;
using System.Data;

namespace Osiptel.HECO.DL
{
    public class GrupoDL
    {

        public int mInsertar(GrupoBE grupo)
        {

            try
            {
                Int32 id = 0;
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_INSERTAR_GRUPO";
                    cmd.CommandType = CommandType.StoredProcedure;

                    OracleParameter prm1 = new OracleParameter("SNOMBRE", OracleType.VarChar);
                    OracleParameter prm2 = new OracleParameter("SDESCRIPCION", OracleType.VarChar);
                    OracleParameter prm3 = new OracleParameter("SCODIGO", OracleType.Char);
                    OracleParameter prm4 = new OracleParameter("SP05TIPO", OracleType.Int32);
                    OracleParameter prm5 = new OracleParameter("SP06SUBTIPO", OracleType.Int32);
                    OracleParameter prm6 = new OracleParameter("SADUSUARIOREG", OracleType.Int32);
                    OracleParameter prm7 = new OracleParameter("SIDGRUPO", OracleType.Int32);

                    prm1.Direction = ParameterDirection.Input;
                    prm2.Direction = ParameterDirection.Input;
                    prm3.Direction = ParameterDirection.Input;
                    prm4.Direction = ParameterDirection.Input;
                    prm5.Direction = ParameterDirection.Input;
                    prm6.Direction = ParameterDirection.Input;
                    prm7.Direction = ParameterDirection.Output;

                    prm1.Value = grupo.Nombre;
                    prm2.Value = grupo.Descripcion;
                    prm3.Value = grupo.Codigo;
                    prm4.Value = grupo.P05Tipo;
                    prm5.Value = grupo.P06Subtipo;
                    prm6.Value = grupo.AdUsuarioReg;
                    prm7.Value = grupo.IdGrupo;

                    cmd.Parameters.Add(prm1);
                    cmd.Parameters.Add(prm2);
                    cmd.Parameters.Add(prm3);
                    cmd.Parameters.Add(prm4);
                    cmd.Parameters.Add(prm5);
                    cmd.Parameters.Add(prm6);
                    cmd.Parameters.Add(prm7);

                    using (DL_CONEXION NewConexion = new DL_CONEXION())
                    {
                        int ret = NewConexion.ejecutaSQL(cmd);
                        id = Convert.ToInt32(Convert.ToString(cmd.Parameters["SIDGRUPO"].Value));
                        return id;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public GrupoBE Obtener(int pIDProyecto)
        {
            try
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_OBTENER_GRUPO";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    OracleParameter prm1 = new OracleParameter("idProy", OracleType.Int32);
                    OracleParameter prm2 = new OracleParameter("oListado", OracleType.Cursor);
                    prm1.Direction = ParameterDirection.Input;
                    prm2.Direction = ParameterDirection.Output;
                    prm1.Value = pIDProyecto;
                    cmd.Parameters.Add(prm1);
                    cmd.Parameters.Add(prm2);

                    using (DL_CONEXION NewDL_CONEXION = new DL_CONEXION())
                    {
                        try
                        {
                            NewDL_CONEXION.retOpen();
                            cmd.Connection = NewDL_CONEXION.retConexion();
                            GrupoBE objConsultaBE = new GrupoBE();
                            using (OracleDataReader oRead = cmd.ExecuteReader())
                            {
                                if (oRead.Read())
                                {
                                    objConsultaBE.IdGrupo = (System.Int32)oRead.GetValue(oRead.GetOrdinal("idgrupo"));
                                    objConsultaBE.Nombre = (System.String)oRead.GetValue(oRead.GetOrdinal("nombre"));
                                    objConsultaBE.Descripcion = (System.String)oRead.GetValue(oRead.GetOrdinal("descripcion"));
                                    objConsultaBE.Codigo = (System.String)oRead.GetValue(oRead.GetOrdinal("codigo"));
                                    objConsultaBE.P05TipoDesc = (System.String)oRead.GetValue(oRead.GetOrdinal("p05tipoDesc"));
                                    objConsultaBE.P06SubtipoDesc = (System.String)oRead.GetValue(oRead.GetOrdinal("p06subtipoDesc"));
                                    objConsultaBE.AdFechaReg = (System.DateTime)oRead.GetValue(oRead.GetOrdinal("adfechareg"));
                                    objConsultaBE.AdFechaModif = (System.DateTime)oRead.GetValue(oRead.GetOrdinal("adfechamodif"));
                                    objConsultaBE.AdUsuarioReg = (System.Int32)oRead.GetValue(oRead.GetOrdinal("adusuarioreg"));
                                    objConsultaBE.AdUsuarioModif = (System.Int32)oRead.GetValue(oRead.GetOrdinal("adusuariomodif"));
                                    objConsultaBE.AdIpReg = (System.String)oRead.GetValue(oRead.GetOrdinal("adipreg"));
                                    objConsultaBE.AdIpModif = (System.String)oRead.GetValue(oRead.GetOrdinal("adipmodif"));

                                }
                                oRead.Close();
                            }
                            NewDL_CONEXION.retClose();
                            return objConsultaBE;
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }

                        finally
                        {
                            if (NewDL_CONEXION.conexion.State == ConnectionState.Open) NewDL_CONEXION.retClose();

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
